﻿using common;
using MySql.Data.MySqlClient;
using System;
using System.Data;
using System.Text;
using System.Threading;
using System.Windows.Forms;

namespace function.database
{
    public partial class UserControl1 : UserControl
    {
        public string str_type = "";
        public string str_init = "";

        public UserControl1()
        {
            InitializeComponent();
        }

        protected override void WndProc(ref System.Windows.Forms.Message m)
        {
            if (m.Msg == 0x210)
            {
                return;
            }

            base.WndProc(ref m);
        }

        private void UserControl1_Load(object sender, EventArgs e)
        {
            this.textBox_type.Items.Add("sqlserver");
            this.textBox_type.Items.Add("mysql");
            this.textBox_type.Items.Add("sqllite");
            this.textBox_type.Items.Add("access");
            this.textBox_type.Text = "sqlserver";

            textBoxServer.Text = "(local)";
            textBoxUid.Text = "sa";
            textBoxPass.Text = "111111";
            textBox_database.Text = "master";

            //this.ContextMenuStrip.Enabled = false;
        }

        private void buttonTestConect_Click(object sender, EventArgs e)
        {
            try
            {
                str_type = this.textBox_type.Text;

                Thread myThread = new Thread(new ThreadStart(this.connect_database));
                myThread.Start();
            }
            catch (Exception ee)
            {
                MessageBox.Show(ee.ToString());
            }
        }

        private void connect_database()
        {
            try
            {
                //*********************************************************
                //
                //*********************************************************
                //if (this.textBoxServer.Text == "")
                //{
                //    MessageBox.Show("非空"); return;
                //}

                //清除treeview
                //treeView1.Nodes.Clear();
                //TreeNode tn1 = new TreeNode();
                //tn1.Text = this.textBoxServer.Text.ToString();
                //tn1.Tag = "root";
                //treeView1.Nodes.Add(tn1);

                //*********************************************************
                //
                //*********************************************************
                string datasource = this.textBoxServer.Text;
                //string database = this.textBoxDataBase.Text;
                string uid = this.textBoxUid.Text;
                string pwd = this.textBoxPass.Text;
                string type = this.textBox_type.Text;
                string database = this.textBox_database.Text;

                //*********************************************************
                //
                //*********************************************************
                if (type == "mysql")
                {
                    string strCon = "server=" + datasource + ";user id=" + uid + "; pooling=false;charset=gb2312; password=" + pwd + "; Allow Zero Datetime=True";
                    MysqlHelper.strConnString = strCon;
                    string strSql = "SHOW DATABASES";

                    MySql.Data.MySqlClient.MySqlDataReader thisReader = MysqlHelper.GetReader(strSql);

                    DataTable dt1 = new DataTable();
                    dt1.Columns.Add("数据库");

                    while (thisReader.Read())
                    {
                        DataRow dr1 = dt1.NewRow();
                        dr1[0] = thisReader[0].ToString();
                        dt1.Rows.Add(dr1);

                        TreeNode tn = new TreeNode();
                        tn.Text = thisReader[0].ToString();
                        tn.Tag = "database";
                        this.treeView1.Nodes[0].Nodes.Add(tn);
                    }

                    this.dataGridViewTable.DataSource = dt1;
                    thisReader.Close();
                }
                else if (type == "sqlserver")
                {
                    string strCon = "";
                    string strSql = "";

                    if (this.textBox_database.Text != "")
                    {
                        strCon = "server=" + datasource + ";database=" + database + ";uid=" + uid + ";pwd=" + pwd + ";";
                        SqlServerHelper.strConnString = strCon;

                        strSql = "use " + textBox_database.Text + ";" + "select * from sysobjects where type='U' order by name";
                        DataTable dt1 = SqlServerHelper.GetDataTable(strSql);
                        this.dataGridViewTable.DataSource = dt1;

                        treeView1.Nodes.Clear();
                        TreeNode tn = new TreeNode();
                        tn.Text = textBox_database.Text;
                        tn.Tag = "database";
                        //tn.Checked = true;
                        //treeView1.SelectedNode = tn;
                        treeView1.Nodes.Add(tn);
                        //treeView1.SelectedNode.Expand();

                        System.Data.SqlClient.SqlDataReader thisReader = SqlServerHelper.GetReader(strSql);
                        while (thisReader.Read())
                        {
                            TreeNode tn1 = new TreeNode();
                            tn1.Text = thisReader[0].ToString();
                            tn1.Tag = "table";

                            treeView1.Nodes[0].Nodes.Add(tn1);
                            //treeView1.SelectedNode.Nodes.Add(tn1);
                        }

                        treeView1.Nodes[0].ExpandAll();
                        thisReader.Close();

                        //strSql = "select * from sys.sysdatabases ";

                        //System.Data.SqlClient.SqlDataReader thisReader = SqlServerHelper.GetReader(strSql);
                        //while (thisReader.Read())
                        //{
                        //    TreeNode tn1 = new TreeNode();
                        //    tn1.Text = thisReader[0].ToString();
                        //    tn1.Tag = "database";
                        //    this.treeView1.Nodes[0].Nodes.Add(tn1);
                        //}
                        //this.treeView1.Sort();
                        //thisReader.Close();
                    }
                    else
                    {
                        MessageBox.Show("请填写数据库");
                        return;
                        ////strCon = "server=" + datasource + ";database=" + "" + ";uid=" + uid + ";pwd=" + pwd + ";";
                        //strCon = @"User ID=sa;Password=111111;Initial Catalog=KBS_REMOTE;Data Source=DBJB0019\ZR;Max Pool Size=1000;DATABASE=KBS_REMOTE";

                        //SqlServerHelper.strConnString = strCon;
                        //strSql = "select * from sys.sysdatabases ";

                        //System.Data.SqlClient.SqlDataReader thisReader = SqlServerHelper.GetReader(strSql);

                        //DataTable dt1 = new DataTable();
                        //dt1.Columns.Add("数据库");

                        //while (thisReader.Read())
                        //{
                        //    DataRow dr1 = dt1.NewRow();
                        //    dr1[0] = thisReader[0].ToString();
                        //    dt1.Rows.Add(dr1);

                        //    TreeNode tn = new TreeNode();
                        //    tn.Text = thisReader[0].ToString();
                        //    tn.Tag = "database";
                        //    this.treeView1.Nodes[0].Nodes.Add(tn);
                        //}

                        //this.treeView1.Sort();

                        //this.dataGridViewTable.DataSource = dt1;
                        //thisReader.Close();
                    }
                }
                else if (type == "sqllite")
                {
                    if (this.textBox_file.Text.Trim() == "") { return; }
                    string file = this.textBox_file.Text.Trim();
                    common.SqliteHelper.strConnString = "Data Source=" + file;
                    //SqliteHelper.ExecuteNonQuery(".tables");
                    //DataTable dt = SqliteHelper.GetDataTable(".tables");
                }
            }
            catch (Exception ee)
            {
                MessageBox.Show("连接失败....");
            }
        }

        private void treeView1_AfterSelect(object sender, TreeViewEventArgs e)
        {
            try
            {
                if (this.treeView1.SelectedNode.Tag.ToString() == "root")
                {
                    treeView1.SelectedNode.Expand();
                    return;
                }

                treeView1.SelectedNode.Nodes.Clear();

                //*********************************************************
                //数据库
                //*********************************************************
                if (this.treeView1.SelectedNode.Tag.ToString() == "database")
                {
                    if (str_type == "mysql")
                    {
                        string strSql = "show tables from " + treeView1.SelectedNode.Text;

                        MySqlDataReader thisReader = MysqlHelper.GetReader(strSql);
                        while (thisReader.Read())
                        {
                            TreeNode tn = new TreeNode();
                            tn.Text = thisReader[0].ToString();
                            tn.Tag = "table";

                            treeView1.SelectedNode.Nodes.Add(tn);
                            treeView1.SelectedNode.Expand();
                        }

                        thisReader.Close();
                    }
                    else if (str_type == "sqlserver")
                    {
                        string strSql = "use " + treeView1.SelectedNode.Text + ";" + "select * from sysobjects where type='U' order by name";

                        System.Data.SqlClient.SqlDataReader thisReader = SqlServerHelper.GetReader(strSql);
                        while (thisReader.Read())
                        {
                            TreeNode tn = new TreeNode();
                            tn.Text = thisReader[0].ToString();
                            tn.Tag = "table";

                            treeView1.SelectedNode.Nodes.Add(tn);
                            treeView1.SelectedNode.Expand();
                        }

                        thisReader.Close();
                    }
                }

                //*********************************************************
                //表
                //*********************************************************
                else if (this.treeView1.SelectedNode.Tag.ToString() == "table")
                {
                    if (str_type == "mysql")
                    {
                        //*********************************************************
                        //
                        //*********************************************************
                        string strDataBase = this.treeView1.SelectedNode.Parent.Text;
                        string strTable = this.treeView1.SelectedNode.Text;
                        string strSql = "select * from " + strDataBase + "." + strTable + " limit 0,100";

                        this.dataGridView1.DataSource = MysqlHelper.GetDataTable(strSql);

                        //*********************************************************
                        //SHOW full COLUMNS FROM assets.device
                        //*********************************************************
                        //strSql = "SHOW COLUMNS FROM " + strDataBase + "." + strTable + "";
                        strSql = "SHOW full COLUMNS FROM " + strDataBase + "." + strTable + "";
                        DataTable dt = MysqlHelper.GetDataTable(strSql);
                        foreach (DataRow dr in dt.Rows)
                        {
                            string field = dr["field"].ToString();
                            string type = dr["type"].ToString();
                            string nullvalue = dr["null"].ToString();
                            string key = dr["key"].ToString();
                            string extra = dr["extra"].ToString();
                            string comment = dr["comment"].ToString();

                            string result = "";
                            result += field;
                            result += " [";
                            result += type + ",";

                            if (nullvalue.ToLower() == "yes")
                            {
                                result += "null" + ",";
                            }
                            else
                            {
                                result += "not null" + ",";
                            }

                            result += key + ",";

                            result += extra + ",";

                            result += comment + "]";

                            TreeNode tn = new TreeNode();
                            tn.Text = result;
                            tn.Tag = "column";
                            treeView1.SelectedNode.Nodes.Add(tn);
                        }
                    }
                    else if (str_type == "sqlserver")
                    {
                        //*********************************************************
                        //
                        //*********************************************************
                        string strDataBase = this.treeView1.SelectedNode.Parent.Text;
                        string strTable = this.treeView1.SelectedNode.Text;
                        string strSql = "select top 100 * from " + strDataBase + ".." + strTable + "";

                        this.dataGridView1.DataSource = SqlServerHelper.GetDataTable(strSql);

                        //*********************************************************
                        //SHOW full COLUMNS FROM assets.device
                        //*********************************************************
                        //strSql = "SHOW COLUMNS FROM " + strDataBase + "." + strTable + "";
                        //strSql = "select   *   from   syscolumns where id=object_id('" + strDataBase + ".." + strTable + "')" ;
                        strSql = "use " + strDataBase + ";select   a.[name] as lieming,b.[name] as leixing    from   syscolumns a,systypes b  where   a.id=object_id('" + strDataBase + ".." + strTable + "') and a.xtype=b.xtype and    b.[name]<>'sysname'";

                        DataTable dt = SqlServerHelper.GetDataTable(strSql);
                        foreach (DataRow dr in dt.Rows)
                        {
                            string field = dr["lieming"].ToString();
                            string type = dr["leixing"].ToString();
                            //string nullvalue = dr["null"].ToString();
                            //string key = dr["key"].ToString();
                            //string extra = dr["extra"].ToString();
                            //string comment = dr["comment"].ToString();

                            string result = "";
                            result += field;
                            result += " [";
                            result += type + "]";

                            //if (nullvalue.ToLower() == "yes")
                            //{
                            //    result += "null" + ",";
                            //}
                            //else
                            //{
                            //    result += "not null" + ",";
                            //}

                            //result += key + ",";

                            //result += extra + ",";

                            //result += comment + "]";

                            TreeNode tn = new TreeNode();
                            tn.Text = result;
                            tn.Tag = "column";
                            treeView1.SelectedNode.Nodes.Add(tn);
                        }
                        treeView1.SelectedNode.ExpandAll();
                    }
                }
            }
            catch (Exception ee)
            {
            }
        }

        private void treeView1_MouseClick(object sender, MouseEventArgs e)
        {
            try
            {
                if (e.Button.ToString() == "Right")
                {
                    string strTemp = this.treeView1.SelectedNode.Text;

                    richTextBox1.Text = strTemp;

                    //string strTextBox = richTextBox1.Text;
                    //string strResult = "";

                    //int i = this.richTextBox1.SelectionStart;
                    //strResult = strTextBox.Substring(0, i);
                    //strResult += strTemp;
                    //strResult += strTextBox.Substring(i, strTextBox.Length - i);

                    //richTextBox1.Text = strResult;
                    //richTextBox1.SelectionStart = i;
                }
            }
            catch (Exception ee) { }
        }

        private void toolStripButton2_Click(object sender, EventArgs e)
        {
            //insert into grade(学号，姓名，数学) values(123,"周润发"，70)

            StringBuilder strTrans = new StringBuilder();

            this.richTextBox1.Text = "";
            if (this.treeView1.SelectedNode == null || this.treeView1.SelectedNode.Tag != "table")
            {
                strTrans.Append("insert into grade(学号，姓名，数学) values(123,\"周润发\"，70)\n");
                this.richTextBox1.Text = strTrans.ToString();
                return;
            }
            else
            {
                if (str_type == "mysql")
                {
                    string database = this.treeView1.SelectedNode.Parent.Text;
                    string table = this.treeView1.SelectedNode.Text;

                    //string strSql = "select * FROM " + database + "." + table + "";
                    //DataTable dt = MysqlHelper.GetDataTable(strSql);

                    string strSql = "SHOW full COLUMNS FROM " + database + "." + table + "";
                    DataTable dt = MysqlHelper.GetDataTable(strSql);

                    string strFields = "";
                    string strValues = "";

                    foreach (DataRow dr in dt.Rows)
                    {
                        string field = dr["field"].ToString();
                        string type = dr["type"].ToString();
                        string nullvalue = dr["null"].ToString();
                        string key = dr["key"].ToString();
                        string extra = dr["extra"].ToString();
                        string comment = dr["comment"].ToString();

                        if (extra.IndexOf("auto_increment") != -1)
                        {
                            continue;
                        }

                        strFields += "`" + field + "`,";
                        strValues += "'1',";
                    }

                    if (strValues.Trim() != "")
                    {
                        strFields = strFields.Substring(0, strFields.Length - 1);
                        strValues = strValues.Substring(0, strValues.Length - 1);

                        strTrans.Append("insert into `" + database + "`.`" + table + "`(" + strFields + ") values(" + strValues + ")\n");
                    }

                    this.richTextBox1.Text = strTrans.ToString();
                    return;
                }
                else
                {
                    strTrans.Append("insert into grade(学号，姓名，数学) values(123,\"周润发\"，70)\n");
                    this.richTextBox1.Text = strTrans.ToString();
                    return;
                }
            }
        }

        private void toolStripButton3_Click(object sender, EventArgs e)
        {
            //delete from grade where 数学＝0

            StringBuilder strTrans = new StringBuilder();

            this.richTextBox1.Text = "";

            if (this.treeView1.SelectedNode == null || this.treeView1.SelectedNode.Tag != "table")
            {
                strTrans.Append("delete from grade where 数学＝0");
                this.richTextBox1.Text = strTrans.ToString();
                return;
            }
            else
            {
                string database = this.treeView1.SelectedNode.Parent.Text;
                string table = this.treeView1.SelectedNode.Text;

                if (str_type == "sqlserver")
                {
                    strTrans.Append("delete from " + database + ".." + table + "");
                }
                else
                {
                    strTrans.Append("delete from `" + database + "`.`" + table + "`");
                }
                this.richTextBox1.Text = strTrans.ToString();
                return;
            }
        }

        /// <summary>
        /// update
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void toolStripButton4_Click(object sender, EventArgs e)
        {
            //update grade set 数学＝数学+10

            StringBuilder strTrans = new StringBuilder();

            this.richTextBox1.Text = "";
            if (this.treeView1.SelectedNode == null || this.treeView1.SelectedNode.Tag != "table")
            {
                strTrans.Append("update grade set 数学＝数学+10 where id=1\n");
                this.richTextBox1.Text = strTrans.ToString();
                return;
            }
            else
            {
                string database = this.treeView1.SelectedNode.Parent.Text;
                string table = this.treeView1.SelectedNode.Text;

                //string strSql = "select * FROM " + database + "." + table + "";
                //DataTable dt = MysqlHelper.GetDataTable(strSql);

                string strSql = "SHOW full COLUMNS FROM " + database + "." + table + "";
                DataTable dt = MysqlHelper.GetDataTable(strSql);

                string strFields = "";
                string strValues = "";

                foreach (DataRow dr in dt.Rows)
                {
                    string field = dr["field"].ToString();
                    string type = dr["type"].ToString();
                    string nullvalue = dr["null"].ToString();
                    string key = dr["key"].ToString();
                    string extra = dr["extra"].ToString();
                    string comment = dr["comment"].ToString();

                    if (extra.IndexOf("auto_increment") != -1)
                    {
                        continue;
                    }

                    strFields += "`" + field + "`='1',";
                }

                strFields = strFields.Substring(0, strFields.Length - 1);
                //strValues = strValues.Substring(0, strValues.Length - 1);

                strTrans.Append("update  `" + database + "`.`" + table + "`set " + strFields + "");
                this.richTextBox1.Text = strTrans.ToString();
                return;
            }
        }

        /// <summary>
        /// select
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void toolStripButton5_Click(object sender, EventArgs e)
        {
            //update grade set 数学＝数学+10

            StringBuilder strTrans = new StringBuilder();

            this.richTextBox1.Text = "";
            if (this.treeView1.SelectedNode == null || this.treeView1.SelectedNode.Tag != "table")
            {
                strTrans.Append("select * from sys.sysobjects   where id=1\n");
                this.richTextBox1.Text = strTrans.ToString();
                return;
            }
            else
            {
                if (str_type == "mysql")
                {
                    string database = this.treeView1.SelectedNode.Parent.Text;
                    string table = this.treeView1.SelectedNode.Text;

                    string strSql = "SHOW full COLUMNS FROM " + database + "." + table + "";
                    DataTable dt = MysqlHelper.GetDataTable(strSql);

                    string strFields = "";
                    string strValues = "";

                    foreach (DataRow dr in dt.Rows)
                    {
                        string field = dr["field"].ToString();
                        string type = dr["type"].ToString();
                        string nullvalue = dr["null"].ToString();
                        string key = dr["key"].ToString();
                        string extra = dr["extra"].ToString();
                        string comment = dr["comment"].ToString();

                        //if (extra.IndexOf("auto_increment") != -1)
                        //{
                        //    continue;
                        //}

                        strFields += "`" + field + "`,";
                    }

                    strFields = strFields.Substring(0, strFields.Length - 1);
                    //strValues = strValues.Substring(0, strValues.Length - 1);

                    strTrans.Append("select " + strFields + " from `" + database + "`.`" + table + "`");
                    this.richTextBox1.Text = strTrans.ToString();
                    return;
                }
                else if (str_type == "sqlserver")
                {
                    string strDataBase = this.treeView1.SelectedNode.Parent.Text;
                    string strTable = this.treeView1.SelectedNode.Text;

                    //string strSql = "SHOW full COLUMNS FROM " + database + "." + table + "";
                    //DataTable dt = MysqlHelper.GetDataTable(strSql);
                    string strSql = "use " + strDataBase + ";select   a.[name] as lieming,b.[name] as leixing    from   syscolumns a,systypes b  where   a.id=object_id('" + strDataBase + ".." + strTable + "') and a.xtype=b.xtype and    b.[name]<>'sysname'";
                    DataTable dt = SqlServerHelper.GetDataTable(strSql);

                    string strFields = "";
                    string strValues = "";

                    foreach (DataRow dr in dt.Rows)
                    {
                        string field = dr["lieming"].ToString();
                        string type = dr["leixing"].ToString();

                        strFields += "" + field + ",";
                    }

                    strFields = strFields.Substring(0, strFields.Length - 1);
                    //strValues = strValues.Substring(0, strValues.Length - 1);

                    string top = this.textBox_config.Text == "" ? "" : "top " + this.textBox_config.Text + " ";
                    strTrans.Append("select " + top + strFields + " from " + strDataBase + ".." + strTable + "");
                    this.richTextBox1.Text = strTrans.ToString();
                    return;
                }
            }
        }

        /// <summary>
        ///执行
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void toolStripLabel1_Click(object sender, EventArgs e)
        {
            zhixing();
        }

        private void zhixing()
        {
            if (this.richTextBox1.SelectedText == "")
            {
                string strSql = richTextBox1.Text;
                ExcuteSql(strSql);
            }
            else
            {
                string strSql = richTextBox1.SelectedText;
                ExcuteSql(strSql);
            }
        }

        private void ExcuteSql(string strSql)
        {
            try
            {
                if (str_type == "mysql")
                {
                    MySqlDataReader thisReader = MysqlHelper.GetReader(strSql);
                    DataTable dt = new DataTable();
                    for (int i = 0; i < thisReader.FieldCount; i++)
                    {
                        dt.Columns.Add(thisReader.GetName(i));
                    }
                    while (thisReader.Read())
                    {
                        DataRow dr1 = dt.NewRow();
                        for (int i = 0; i < thisReader.FieldCount; i++)
                        {
                            dr1[i] = thisReader[i].ToString();
                        }
                        dt.Rows.Add(dr1);
                    }
                    this.dataGridView1.DataSource = dt;
                    dt = MysqlHelper.DealTable_Str(dt);
                    thisReader.Close();

                    this.tabControl1.SelectedTab = tabPageResult;
                    richTextBoxMessage.Text = "";
                }
                else if (str_type == "sqlserver")
                {
                    DataTable dt = SqlServerHelper.GetDataTable(strSql);

                    //SqlDataReader thisReader = SqlServerHelper.GetReader(strSql);
                    //DataTable dt = new DataTable();
                    //for (int i = 0; i < thisReader.FieldCount; i++)
                    //{
                    //    dt.Columns.Add(thisReader.GetName(i));
                    //}
                    //while (thisReader.Read())
                    //{
                    //    DataRow dr1 = dt.NewRow();
                    //    for (int i = 0; i < thisReader.FieldCount; i++)
                    //    {
                    //        dr1[i] = thisReader[i].ToString();
                    //    }
                    //    dt.Rows.Add(dr1);
                    //}
                    this.dataGridView1.DataSource = dt;
                    //dt = MysqlHelper.DealTable_Str(dt);
                    //thisReader.Close();

                    this.tabControl1.SelectedTab = tabPageResult;
                    richTextBoxMessage.Text = "";
                }
            }
            catch (Exception ee)
            {
                this.tabControl1.SelectedTab = tabPageMessage;
                richTextBoxMessage.Text = ee.ToString();

                this.dataGridView1.DataSource = null;
            }
        }

        private void UserControl1_MouseClick(object sender, MouseEventArgs e)
        {
        }
    }
}